package com.example.shiwu.manager;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.example.shiwu.util.StringUtil;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String TAG = "DatabaseHelper";

    public static final int TABLE_VERSION = 1;

    public static final String TABLE_NAME = "shiwu.db";
    public static final String NAME = "user";
    public static final String COLUMN_ID = "id";//long类型的id不能自增，primary key autoincrement会出错
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_STATUS = "status";


    public DatabaseHelper(Context context) {
        super(context, TABLE_NAME, null, TABLE_VERSION);
    }
    public DatabaseHelper() {
        super(null,TABLE_NAME, null, TABLE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + NAME + " (" + COLUMN_ID + " Integer primary key autoincrement, "
                + COLUMN_NAME + " varchar(20), " + COLUMN_PASSWORD + " varchar(120), " + COLUMN_STATUS + " Integer DEFAULT 0)";

        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }



    /**根据Cursor获取内容
     * @param cursor
     * @return
     */
    public ContentValues getValue(Cursor cursor) {
        ContentValues values = null;
        if (cursor != null) {
            values = new ContentValues();

            if (cursor.moveToFirst()) {
                values.put(COLUMN_ID, cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                values.put(COLUMN_NAME, cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
                values.put(COLUMN_PASSWORD, cursor.getString(cursor.getColumnIndex(COLUMN_PASSWORD)));
                values.put(COLUMN_STATUS, cursor.getString(cursor.getColumnIndex(COLUMN_STATUS)));
            }
            cursor.close();
        }
        return values;
    }
    /**根据Cursor获取内容
     * @param cursor
     * @return
     */
    public List<ContentValues> getValueList(Cursor cursor) {
        List<ContentValues> list = null;
        if (cursor != null) {
            list = new ArrayList<ContentValues>();

            ContentValues values;
            while (cursor.moveToNext()) {
                values = new ContentValues();
                values.put(COLUMN_ID, cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                values.put(COLUMN_NAME, cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
                values.put(COLUMN_PASSWORD, cursor.getString(cursor.getColumnIndex(COLUMN_PASSWORD)));
                values.put(COLUMN_STATUS, cursor.getString(cursor.getColumnIndex(COLUMN_STATUS)));

                list.add(values);
            }
            cursor.close();
        }
        return list;
    }

    /**获取正确的的ContentValues，防止数据库操作出错
     * @param values
     * @return
     */
    public ContentValues getCorrectValues(ContentValues values) {
        if (values == null || values.size() <= 0) {
            return null;
        }

        //去除所有空key
        Set<String> set = values.keySet();
        for (String key : set) {
            if (StringUtil.isNotEmpty(key, true) == false) {
                values.remove(key);
            }
        }

        return values;
    }


    /**插入数据
     * @param id
     * @param values - 一行键值对数据
     * @return
     */
    public void put(int id, ContentValues values) {
        put(COLUMN_ID, "" + id, values);
    }
    /**插入数据
     * @param column - 列名称
     * @param value - 筛选数据的条件值
     * @param values - 一行键值对数据
     */
    public void put(String column, String value, ContentValues values) {
        ContentValues oldValues = get(column, value);
        if (oldValues != null && oldValues.containsKey(COLUMN_ID)
                && StringUtil.isNotEmpty(oldValues.get(COLUMN_ID), true)) {//数据存在且有效
            update(column, value, values);
        } else {
            insert(values);
        }
    }

    /**获取单个数据
     * @param id
     * @return
     */
    public ContentValues get(int id) {
        return getValue(query(id));
    }
    /**获取单个数据
     * @param column
     * @param value
     * @return
     */
    public ContentValues get(String column, String value) {
        return getValue(query(column, value));
    }
    /**获取数据列表
     * @param column
     * @param value
     * @return
     */
    public List<ContentValues> getList(String column, String value) {
        return getValueList(query(column, value));
    }
    /**获取所有数据
     * @param
     * @return
     */
    public List<ContentValues> getAll() {
        return getList(null, null);
    }

    /**插入数据
     * @param values
     * @return
     */
    public long insert(ContentValues values) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            return db.insert(TABLE_NAME, null, getCorrectValues(values));
        } catch (Exception e) {
            Log.e(TAG, "update   try { return db.insert(.... } catch (Exception e) {\n " + e.getMessage());
        }
        return -1;
    }

    /**更新数据
     * @param id
     * @param values
     * @return
     */
    public int update(int id, ContentValues values) {
        return update(COLUMN_ID, "" + id, values);
    }
    /**更新数据
     * @param column
     * @param value
     * @param values
     * @return
     */
    public int update(String column, String value, ContentValues values) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            return db.update(TABLE_NAME, getCorrectValues(values), getSelection(column), getSelectionArgs(column, value));
        } catch (Exception e) {
            Log.e(TAG, "update   try { return db.update(.... } catch (Exception e) {\n " + e.getMessage());
        }
        return 0;
    }

    /**删除数据
     * @param id
     * @return
     */
    public int delete(int id) {
        return delete(COLUMN_ID, "" + id);
    }
    /**删除数据
     * @param column
     * @param value
     * @return
     */
    public int delete(String column, String value) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            return db.delete(TABLE_NAME, getSelection(column), getSelectionArgs(column, value));
        } catch (Exception e) {
            Log.e(TAG, "update   try { return db.delete(.... } catch (Exception e) {\n " + e.getMessage());
        }
        return 0;
    }

    /**查询所有数据
     * @return
     */
    public Cursor queryAll() {
        SQLiteDatabase db = this.getReadableDatabase();
        try {
            return db.query(TABLE_NAME, null, null, null, null, null, null);
        } catch (Exception e) {
            Log.e(TAG, "queryAll  try { return db.query(...} catch (Exception e) {\n" + e.getMessage());
        }
        return null;
    }
    /**查询单个数据
     * @param id
     * @return
     */
    public Cursor query(int id) {
        return query(COLUMN_ID, "" + id);
    }
    /**查询单个数据
     * @param column
     * @param value
     * @return
     */
    public Cursor query(String column, String value) {
        SQLiteDatabase db = this.getReadableDatabase();
        try {
            return db.query(TABLE_NAME, null, getSelection(column), getSelectionArgs(column, value), null, null, null);
        } catch (Exception e) {
            Log.e(TAG, "query  try { return db.query(...} catch (Exception e) {\n" + e.getMessage());
        }
        return null;
    }



    /**获取过滤条件类型
     * @param column
     * @return StringUtil.isNotEmpty(column, false) ? column + " = ?" : null
     */
    private String getSelection(String column) {
        return StringUtil.isNotEmpty(column, false) ? column + " = ?" : null;
    }
    /**获取过滤条件值
     * @param column
     * @return StringUtil.isNotEmpty(column, false) ? new String[]{value} : nul
     */
    private String[] getSelectionArgs(String column, String value) {
        return StringUtil.isNotEmpty(column, false) ? new String[]{value} : null;
    }
}

